package com.uwaterloo.interviewme.am.udm;

import static com.uwaterloo.interviewme.common.DBConstants.CNAME;
import static com.uwaterloo.interviewme.common.DBConstants.DATABASE_CREATE;
import static com.uwaterloo.interviewme.common.DBConstants.DATABASE_NAME;
import static com.uwaterloo.interviewme.common.DBConstants.DATABASE_TABLE;
import static com.uwaterloo.interviewme.common.DBConstants.DATABASE_VERSION;
import static com.uwaterloo.interviewme.common.DBConstants.ID;
import static com.uwaterloo.interviewme.common.DBConstants.JDESC;
import static com.uwaterloo.interviewme.common.DBConstants.LENGTH;
import static com.uwaterloo.interviewme.common.DBConstants.LOCATION;
import static com.uwaterloo.interviewme.common.DBConstants.NA;
import static com.uwaterloo.interviewme.common.DBConstants.POSITION;
import static com.uwaterloo.interviewme.common.DBConstants.STATUS;
import static com.uwaterloo.interviewme.common.DBConstants.TIME;
import static com.uwaterloo.interviewme.common.DBConstants.TYPE;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.uwaterloo.interviewme.model.DetailInfo;
import com.uwaterloo.interviewme.model.Interview;

/**
 * The Class InterviewDbAdapter provides utility function for CUID operations of the underlying
 * SQLite.
 */
public class InterviewDbAdapter {

	/** The Constant TAG. */
	private static final String TAG = "InterviewDbAdapter";

	/** The ctx. */
	private Context ctx;

	/** The db helper. */
	private DatabaseHelper dbHelper;

	/** The db. */
	private SQLiteDatabase db;

	/**
	 * The Class DatabaseHelper.
	 */
	private static class DatabaseHelper extends SQLiteOpenHelper {

		/**
		 * Instantiates a new database helper.
		 * 
		 * @param context
		 */
		DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		/*
		 * (non-Javadoc)
		 * 
		 * @see
		 * android.database.sqlite.SQLiteOpenHelper#onCreate(android.database.sqlite.SQLiteDatabase)
		 */
		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(DATABASE_CREATE);
		}

		/*
		 * (non-Javadoc)
		 * 
		 * @see
		 * android.database.sqlite.SQLiteOpenHelper#onUpgrade(android.database.sqlite.SQLiteDatabase
		 * , int, int)
		 */
		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w(TAG, "Upgrading database from version " + oldVersion + " to " + newVersion + ", which will destroy all old data");
			db.execSQL("DROP TABLE IF EXISTS notes");
			onCreate(db);
		}
	}

	/**
	 * Instantiates a new interview db adapter.
	 * 
	 * @param ctx
	 */
	public InterviewDbAdapter(Context ctx) {
		this.ctx = ctx;
	}

	/**
	 * Open.
	 * 
	 * @throws SQLException
	 */
	public void open() throws SQLException {
		dbHelper = new DatabaseHelper(ctx);
		db = dbHelper.getWritableDatabase();
	}

	/**
	 * Close.
	 */
	public void close() {
		db.close();
	}

	/**
	 * Insert interview.
	 * 
	 * @param itv
	 * @return the long
	 */
	public long insertInterview(Interview itv) {
		ContentValues initValue = new ContentValues();
		initValue.put(ID, itv.getInterviewID());
		initValue.put(CNAME, itv.getCompanyName());
		initValue.put(POSITION, itv.getPosition());
		initValue.put(LOCATION, itv.getLocation());
		initValue.put(TIME, itv.getTime());
		initValue.put(TYPE, NA);
		initValue.put(STATUS, NA);
		initValue.put(LENGTH, NA);
		initValue.put(JDESC, NA);
		return db.insert(DATABASE_TABLE, null, initValue);
	}

	/**
	 * Update interview.
	 * 
	 * @param itv
	 * @return true, if successful
	 */
	public boolean updateInterview(Interview itv) {
		DetailInfo dInfo = itv.getDetailInfo();
		ContentValues initValue = new ContentValues();
		initValue.put(TYPE, dInfo.getType());
		initValue.put(STATUS, dInfo.getStatus());
		initValue.put(LENGTH, dInfo.getLength());
		initValue.put(JDESC, dInfo.getJobDesc());

		String interviewID = itv.getInterviewID();
		return db.update(DATABASE_TABLE, initValue, ID + "=\'" + interviewID + '\'', null) > 0;
	}

	/**
	 * Delete interview.
	 * 
	 * @param interviewID
	 * @return true, if successful
	 */
	public boolean deleteInterview(String interviewID) {
		return db.delete(DATABASE_TABLE, ID + "=\'" + interviewID + "\'", null) > 0;
	}

	/**
	 * Fetch all interviews.
	 * 
	 * @return the cursor
	 */
	public Cursor fetchAllInterviews() {
		return db.query(DATABASE_TABLE, new String[] { ID, CNAME, POSITION, LOCATION, TIME }, null, null, null, null, null);
	}

	/**
	 * Fetch interview.
	 * 
	 * @param interviewID
	 * @return the cursor
	 * @throws SQLException
	 */
	public Cursor fetchInterview(String interviewID) throws SQLException {
		Cursor mCursor = db.query(true, DATABASE_TABLE, new String[] { ID, CNAME, POSITION, LOCATION, TIME, TYPE, STATUS, LENGTH, JDESC },
				ID + "=\'" + interviewID + "\'", null, null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}
}
